import com.mongodb.BasicDBObject;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.model.Filters;
import com.mongodb.client.model.UpdateOptions;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.Document;

import java.io.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class Main {

    static MongoClient mongoClient = new MongoClient("192.168.1.173", 27017);
    static MongoDatabase mDatabase = mongoClient.getDatabase("2021年新增高管数据");
    static MongoCollection collection = mDatabase.getCollection("2021年新增高管数据（用于提取院校）(含ID)");


    public static void main(String[] args) throws IOException, InvalidFormatException {

        //FileInputStream in = new FileInputStream(new File("D:\\测试临时文件\\Balance of payments by country - annual data (BPM6).xlsx"));



        String tempPath = "D:/测试临时文件/2021年新增高管数据（用于提取院校）(含ID).xlsx";
        //Workbook workbook= WorkbookFactory.create(new FileInputStream(tempPath));
        // Workbook workbook = new XSSFWorkbook(new FileInputStream(tempPath));
        //Workbook workbook = WorkbookFactory.create(new FileInputStream(tempPath));
        Workbook workbook = WorkbookFactory.create(new File(tempPath));
        Sheet sheet = workbook.getSheetAt(0);

        List<String> KeySet = new ArrayList<>();

        int n = sheet.getRow(0).getPhysicalNumberOfCells();
        Row rowTemp = sheet.getRow(0);
        for (int i = 0; i < n; i++){
            KeySet.add(rowTemp.getCell(i)+"");
        }

        int rowCount = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < rowCount; i++){
            Row row = sheet.getRow(i);

            int cellCount = row.getPhysicalNumberOfCells();
            Document doc = new Document();

            for (int j = 0; j < cellCount; j++){
                Cell cell = row.getCell(j);
                String cellVal = "";

                cellVal = cell + "";
                doc.append(KeySet.get(j), cellVal);
            }

            try {
               collection.insertOne(doc);
                System.out.println("写入成功：   " + doc.get("_id"));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}
